An extremely fast binary database example in VB 4/32 code using variable length-records.
Copyright ⌐ 1997, Mike McKee
LICENSE:
This is free. You may distribute it as long as this readme.txt file is included in the zip file along with the Visual Basic 4/32 code. Although optional to you, I ask that if you use this code that you stick the notice:
Binary DB portions Copyright ⌐ 1997, Shortcut Systems Software
in your About box--just because of the "warm fuzzy" effect it gives me. Or, you can email me at shortcut@ntwrks.com and say you used it. : )
INTRODUCTION:
Face it. The Jet database engine isn't always the best thing in the world to use, although it is extremely convenient. Some of the major flaws with Jet is that it is a slow implementation in VB, that it has large libraries you must distribute, and that Microsoft (and other vendors) are often guilty of using bad programming to overwrite these DLLs during setup programs--causing strange effects that can hurt your application's market reliability.
Your third-party database engine alternatives, like btrieve and others, have the disadvantage of long ramp-up times to understand the APIs. The libraries can also be large.
Another alternative worth mentioning is ISAM text files, but this requires distribution of JET DLLs and is subject to what I've mentioned earlier--overwriting.
The random access method of accessing files is not practical at all because it requires fixed-length records--who uses those these days?. Some people get around this by keeping an entirely different file for each variable length record, then drop the filename of it in a random access database field, but that's a kludge and it can fill up a hard drive real quick.
The binary access method is really the only viable alternative and can give you great speed advantages, but you must answer some fundamental questions:
* What do you do when a record gets edited and grows larger than its alotted space?
* What do you do when you delete a record?
* How do you lookup records fast?
* How do you list a table of some of the fields very fast?
* With Jet and other major vendor db products, queries can be achieved easily--how do you do this with the binary access method?
* How do add encryption?
* How do you SQL query capability?
* How do you add binary sort?
The way I answered some of these questions was by using two files--one for the variable-length records, and one as an index file. (When you run the program, it creates these files automatically.) The index file is actually nothing more than an INI file for simplicity and speed. (Also, it's a good idea to keep the index as a file you can edit to extract data out of the record file in case of an emergency.) . The index file contains the starting byte of the binary file's record, the length of the record, and then all data necessary to display for a table of records in the fastest way we can. The record file is a binary text file. When a record gets edited, it gets appended to the bottom of the index and binary record file. Also, the lookup in the index points to this new position--thereby "orphaning" (aka tombstoning to ex-dBase programmers out there) the old record. During shutdown, it does a simple compaction on the binary record file to read out only the necessary bytes into a new file, then delete the old file, then copy the new file as the old file.
ROOM FOR GROWTH:
* I didn't work too hard on the GUI--you can do that on your own.
* You can improve the binary database' compaction method by making it:
- make a backup of the current index file before compaction.
- if you prefer, you can let it leave behind a backup of the binary db record file just before compaction.
- using some registry setting or INI file setting that can be checked and, every 10th time, a reindexing can be set to occur with the index file. A reindexing would mean make each record in the INI file fall contiguously in indexed order as 1,2,3, rather than 2, 110, 400 (for example).
* If you have more than just one variable-length record, you can edit the BinaryMgmt module to including these records.
* You can turn BinaryMgmt module into a class object. Additionally, you could make it mimic Jet (DAO) access methods. (If you do this, I would be interested in receiving your code at shortcut@ntwrks.com.)
* You can put two VBCRLFs after each record in the binary db record file so that it might be better readable from a text editor.
* You can change the parse methods in StringMgmt module to lookup a two-character delimiter rather than a one-character--just so you decrease the odds that someone may be using that character.
* If I were to be asked to add a find function, I would let it first search the INI file with standard INI read requests, caching the start and len bytes in an array as I went along, and then I would let it search the binary db record file. If it found an entry in the binary db record file, I would make it compare against the start and len bytes in the array to see if it fell between any entries. If it did, I would highlight this record or make it open up. To improve lookup speed, you could use a Computer Science algorithms text book and index the database in another file and do binary tree sorts and two-way binary tree sorts (like Rushmore technology in FoxPro).
* Add SQL capability. I recommend that you investigate supporting a subset of this SQL language, and, by your own code, parse it, and conduct the queries by either searching the index file, which is relatively fast and easy, or, if you must go into the binary db record file, loading this file into a byte array in memory and search it that way.
* Binary sort algorithm. Many are available for download as a news message if you search at http://www.dejanews.com .
* Encryption. Simple encryption can be achieved from the VBPJ article on algorithms in September 1995 issue. Or, you can download harder ones from http://www.dejanews.com messages. Or, you can download uncrackable ones using an OCX from a manufacturer--I suggest sticking with RSA encryption.
* It is my strong recommendation that you use a separate file for each table to reduce chance of data corruption.
